package com.example.pdm_project_group_001;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;



public class DBHelper extends SQLiteOpenHelper  {

	private static final String DATABASE_NAME = "RouteData.db";
	private static final int DATABASE_VERSION = 7;

	public static final String TABLE_RouteData = "RouteData";
	public static final String col1_locAName = "locAName";
	public static final String col2_locBName = "locBName";
	public static final String col1_locAPos = "locAPos";
	public static final String col2_locBPos = "locBPos";
	public static final String col3_busNo = "busNo";

	public static final String[] all_columns ={col1_locAPos, col1_locAName,
												col2_locBPos, col2_locBName,
												col3_busNo};
	
	// Database creation sql statement
	  private static final String DATABASE_CREATE = "create table "
	      + TABLE_RouteData + "(	"
	      + "id integer primary key autoincrement, "
	      + col1_locAPos  +" text not null, "
	      + col1_locAName  +" text not null, "
		  + col2_locBPos  +" text not null, "
		  + col2_locBName  +" text not null, "
		  + col3_busNo +" text not null);";
 
	//---------------------------------------------------------------------------------
		public static final String TABLE_BusRoutes = "BusRoutes";
		public static final String BusRoutes_locA = "locA";
		public static final String BusRoutes_locB = "locB";
		public static final String BusRoutes_busNo = "busNo";
		public static final String BusRoutes_distance = "distance";
		public static final String[] all_columns_BusRoutes = {	BusRoutes_locA,
																BusRoutes_locB,
																BusRoutes_busNo,
																BusRoutes_distance	};
		private static final String create_table_BusRoutes = "CREATE TABLE "+TABLE_BusRoutes+"("
																+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
																+ BusRoutes_locA+" TEXT NOT NULL, "
																+ BusRoutes_locB+" TEXT NOT NULL, "
																+ BusRoutes_busNo+" TEXT NOT NULL, "
																+ BusRoutes_distance+" TEXT NOT NULL );";
		//---------------------------------------------------------------------------------
		//---------------------------------------------------------------------------------
				public static final String TABLE_TrainRoutes = "TrainRoutes";
				public static final String TrainRoutes_locA = "locA";
				public static final String TrainRoutes_locB = "locB";
				public static final String TrainRoutes_busNo = "trainNo";
				public static final String TrainRoutes_distance = "distance";
				public static final String[] all_columns_TrainRoutes = {	TrainRoutes_locA,
																			TrainRoutes_locB,
																			TrainRoutes_busNo,
																			TrainRoutes_distance	};
				private static final String create_table_TrainRoutes = "CREATE TABLE "+TABLE_TrainRoutes+"("
																		+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
																		+ TrainRoutes_locA+" TEXT NOT NULL, "
																		+ TrainRoutes_locB+" TEXT NOT NULL, "
																		+ TrainRoutes_busNo+" TEXT NOT NULL, "
																		+ TrainRoutes_distance+" TEXT NOT NULL );"	;
		//---------------------------------------------------------------------------------

				//---------------------------------------------------------------------------------
				public static final String TABLE_BusHalts = "BusHalts";
				public static final String BusHalts_locName = "locName";
				public static final String BusHalts_locPos = "locPos";
				public static final String[] all_columns_BusHalts = {	BusHalts_locName,
																			BusHalts_locPos };
				private static final String create_table_BusHalts = "CREATE TABLE "+TABLE_BusHalts+"("
																		+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
																		+ BusHalts_locName+" TEXT NOT NULL, "
																		+ BusHalts_locPos+" TEXT NOT NULL );";
		//---------------------------------------------------------------------------------
		//---------------------------------------------------------------------------------
				public static final String TABLE_TrainStations = "TrainStations";
				public static final String TrainStations_locName = "locName";
				public static final String TrainStations_locPos = "locPos";
				public static final String[] all_columns_TrainStations = {	TrainStations_locName,
																			TrainStations_locPos };
				private static final String create_table_TrainStations = "CREATE TABLE "+TABLE_TrainStations+"("
																		+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
																		+ TrainStations_locName+" TEXT NOT NULL, "
																		+ TrainStations_locPos+" TEXT NOT NULL );";
		//---------------------------------------------------------------------------------

	  
	  
	  public DBHelper(Context context) {
	    super(context, DATABASE_NAME, null, DATABASE_VERSION);
	  }

	  @Override
	  public void onCreate(SQLiteDatabase database) {
		  	database.execSQL(create_table_BusRoutes);
		    Log.w(DBHelper.class.getName(),("create_table_BusRoutes") );
		    
		    database.execSQL(create_table_TrainRoutes);
		    Log.w(DBHelper.class.getName(),("create_table_TrainRoutes") );
		    
		    database.execSQL(create_table_BusHalts);
		    Log.w(DBHelper.class.getName(),("create_table_BusHalts") );
		    
		    database.execSQL(create_table_TrainStations);
		    Log.w(DBHelper.class.getName(),("create_table_TrainStations") );
		    
		    database.execSQL("INSERT INTO "+TABLE_BusHalts+" ("+all_columns_BusHalts[0]+","+all_columns_BusHalts[1]+") VALUES('Kaduwela'		, '6.936257,79.983194');");
		    Log.w(DBHelper.class.getName(),("insert data 1") );
		    database.execSQL("INSERT INTO "+TABLE_BusHalts+" ("+all_columns_BusHalts[0]+","+all_columns_BusHalts[1]+") VALUES('Malabe SLIIT'	, '6.914509,79.972165');");
		    Log.w(DBHelper.class.getName(),("insert data 2") );
		    database.execSQL("INSERT INTO "+TABLE_BusHalts+" ("+all_columns_BusHalts[0]+","+all_columns_BusHalts[1]+") VALUES('Malabe'			, '6.90487,79.949913');");
		    Log.w(DBHelper.class.getName(),("insert data 3") );
		    database.execSQL("INSERT INTO "+TABLE_BusHalts+" ("+all_columns_BusHalts[0]+","+all_columns_BusHalts[1]+") VALUES('Battaramulla'	, '6.901973,79.917898');");
		    Log.w(DBHelper.class.getName(),("insert data 4") );
		    database.execSQL("INSERT INTO "+TABLE_BusHalts+" ("+all_columns_BusHalts[0]+","+all_columns_BusHalts[1]+") VALUES('Rajagiriya'		, '6.910067,79.894509');");
		    Log.w(DBHelper.class.getName(),("insert data 5") );
		    database.execSQL("INSERT INTO "+TABLE_BusHalts+" ("+all_columns_BusHalts[0]+","+all_columns_BusHalts[1]+") VALUES('Kollupitiya'		, '6.911857,79.850435');");
		    Log.w(DBHelper.class.getName(),("insert data 6") );
		    
		    database.execSQL("INSERT INTO "+TABLE_BusRoutes+" ("+all_columns_BusRoutes[0]+
					","+all_columns_BusRoutes[1]+
					","+all_columns_BusRoutes[2]+
					","+all_columns_BusRoutes[3]+
					") 		VALUES("
							+ "'1'	"
							+ ", '2'"
							+ ", '177'"
							+ ", '3.1'"
							+ ");");
		    
		    database.execSQL("INSERT INTO "+TABLE_BusRoutes+" ("+all_columns_BusRoutes[0]+
					","+all_columns_BusRoutes[1]+
					","+all_columns_BusRoutes[2]+
					","+all_columns_BusRoutes[3]+
					") 		VALUES("
							+ "'2'	"
							+ ", '3'"
							+ ", '177'"
							+ ", '2.7'"
							+ ");");
		    
		    database.execSQL("INSERT INTO "+TABLE_BusRoutes+" ("+all_columns_BusRoutes[0]+
					","+all_columns_BusRoutes[1]+
					","+all_columns_BusRoutes[2]+
					","+all_columns_BusRoutes[3]+
					") 		VALUES("
							+ "'3'	"
							+ ", '4'"
							+ ", '177'"
							+ ", '4.6'"
							+ ");");
		    
		    database.execSQL("INSERT INTO "+TABLE_BusRoutes+" ("+all_columns_BusRoutes[0]+
					","+all_columns_BusRoutes[1]+
					","+all_columns_BusRoutes[2]+
					","+all_columns_BusRoutes[3]+
					") 		VALUES("
							+ "'4'	"
							+ ", '5'"
							+ ", '177'"
							+ ", '2.2'"
							+ ");");

		    database.execSQL("INSERT INTO "+TABLE_BusRoutes+" ("+all_columns_BusRoutes[0]+
					","+all_columns_BusRoutes[1]+
					","+all_columns_BusRoutes[2]+
					","+all_columns_BusRoutes[3]+
					") 		VALUES("
							+ "'5'	"
							+ ", '6'"
							+ ", '177'"
							+ ", '5.8'"
							+ ");");

		    
		    Log.w(DBHelper.class.getName(),("insert data routes..") );
		    
	  }

	  @Override
	  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    Log.w(DBHelper.class.getName(),
	        "Upgrading database from version " + oldVersion + " to "
	            + newVersion + ", which will destroy all old data");
	    
	    db.execSQL("DROP TABLE IF EXISTS " + TABLE_RouteData);
	    Log.w(DBHelper.class.getName(),("drop TABLE_RouteData") );

	    db.execSQL("DROP TABLE IF EXISTS " + TABLE_BusRoutes);
	    Log.w(DBHelper.class.getName(),("drop TABLE_BusRoutes") );
	    
	    db.execSQL("DROP TABLE IF EXISTS " + TABLE_TrainRoutes);
	    Log.w(DBHelper.class.getName(),("drop TABLE_TrainRoutes") );
	    
	    db.execSQL("DROP TABLE IF EXISTS " + TABLE_BusHalts);
	    Log.w(DBHelper.class.getName(),("drop TABLE_BusHalts") );
	    
	    db.execSQL("DROP TABLE IF EXISTS " + TABLE_TrainStations);
	    Log.w(DBHelper.class.getName(),("drop TABLE_TrainStations") );
	    
	    onCreate(db);
	  }
}
